

/**
	AUTHOR		: SUJAN
	DATE		: MARCH 03rd 2005
	DESCRIPTION	: THIS FUNCTION IS USED TO GET THE DETAILS OF THE SALES BACKLOG BY HOLD/OPEN AND BY REQUESTED/SCHEDULED DATE .
*/

ALTER  FUNCTION FN_FORECAST_LOAD_SALES_BACKLOG(

@CUSTOMER_ID INT,
@PRODUCT_ID INT,
@DATE_CATEGORY INT, 	-- 0 = 'BY REQUESTED DATE', 1 = 'BY SCHEDULED DATE'
@MONTH_FROM INT, 	-- STARTS FROM ZERO.
@SHOW_NEXT INT, 	--NUMBER OF MONTHS TO BE FETCHED STARTING FROM  @MONTH_FROM. STARTS FROM 1. 
@YEAR_START INT, 	--STARTING YEAR
@AS_OF_DATE VARCHAR(20),
@BACKLOG_TYPE INT )	-- 0 = 'OPEN', 1 = 'HOLD'

RETURNS @FN_RESULTS TABLE( USR VARCHAR(50), CUSTOMER  VARCHAR(100), PRODUCT VARCHAR(100), 
MONTH INT, YEAR INT, QUANTITY INT, UNIT_PRICE FLOAT, REQ_DATE datetime, 
SCH_DATE datetime,BL_TYPE VARCHAR(30), END_USER VARCHAR(50) ) 

AS
BEGIN


	DECLARE @SALES_PERSON VARCHAR(50)
	DECLARE @END_USER VARCHAR(50)
	DECLARE @CUSTOMER VARCHAR(100)
	DECLARE @PRODUCT VARCHAR(50)
	DECLARE @MONTH INT
	DECLARE @YEAR INT
	DECLARE @QUANTITY INT
	DECLARE @OLD_QUANTITY INT
	DECLARE @OLD_PRICE FLOAT   
	DECLARE @UNIT_PRICE FLOAT
	DECLARE @REQ_DATE datetime
	DECLARE @SCH_DATE datetime
	DECLARE @BL_TYPE VARCHAR(20)
	DECLARE @BACKLOGTYPE VARCHAR(30) 
	DECLARE @TEMP_CURSOR_INIT INT
	DECLARE @ROW_COUNT INT
	DECLARE @MKTG_PART_NUMBER VARCHAR(100)
	DECLARE @SALES_ORDER_NUMBER  VARCHAR(50)
	DECLARE @LINEITEM_NO INT 
	

	SET @TEMP_CURSOR_INIT = 1

	DECLARE @START_MONTH INT
	DECLARE @COUNT_MONTHS INT

	IF @MONTH_FROM < 0
	BEGIN
		SET @START_MONTH = 0
	END
	ELSE
	BEGIN
		SET @START_MONTH = @MONTH_FROM
	END

	IF @SHOW_NEXT <= 0
	BEGIN
		SET @COUNT_MONTHS = 11
	END
	ELSE
	BEGIN
		SET @COUNT_MONTHS = @SHOW_NEXT
	END
	
	DECLARE @END_MONTH INT	
	SET @END_MONTH = @START_MONTH + @COUNT_MONTHS 

	DECLARE @TO_YEAR INT	
	SET @TO_YEAR = @YEAR_START + @END_MONTH/12 

	DECLARE @TO_MONTH INT
	SET @TO_MONTH = @END_MONTH%12		


	DECLARE @YEAR_TO INT

	SET @MONTH_FROM = @MONTH_FROM + 1
	SET @SHOW_NEXT = @SHOW_NEXT + 1

	SET @YEAR_TO = @YEAR_START + @SHOW_NEXT/12
	SET @SHOW_NEXT = @SHOW_NEXT%12

	
	DECLARE @CATEGORY VARCHAR(30)
	
	

	IF @PRODUCT_ID IS NULL OR @PRODUCT_ID <= 0	 
	BEGIN
		SELECT @PRODUCT_ID = 0
	END

	IF @CUSTOMER_ID IS NULL OR @CUSTOMER_ID <= 0	 
	BEGIN
		SELECT @CUSTOMER_ID = 0
	END

	DECLARE @TEMP_CURSOR CURSOR

	IF @CUSTOMER_ID IS NOT NULL AND @PRODUCT_ID IS NOT NULL
	BEGIN
	
		IF @DATE_CATEGORY = 0
		BEGIN
			SET @CATEGORY = 'reqDockDate'
		END
		
		ELSE IF @DATE_CATEGORY = 1
		BEGIN
			SET @CATEGORY = 'ScheduleDate'
		END
		
		IF @BACKLOG_TYPE = 0
		BEGIN
			SET @BACKLOGTYPE = 'OPEN'
		END
		
		ELSE IF @BACKLOG_TYPE = 1
		BEGIN
			SET @BACKLOGTYPE = 'HOLD'
		END

		SET @TEMP_CURSOR_INIT = 0


	IF(@BACKLOG_TYPE = 0 OR @BACKLOG_TYPE = 1)  
	BEGIN

		SET @TEMP_CURSOR = CURSOR FOR

		SELECT SALES_PERSON, CUSTOMER_NAME, PART_NUMBER, BOOKINGMONTH-1, BOOKINGYEAR, REM_QTY, 
		UNIT_PRICE, REQ_DATE, SCHD_DATE, BACKLOGTYPE 
		FROM fn_backlog_report( @PRODUCT_ID, '0', @CUSTOMER_ID, '0', @AS_OF_DATE, 'all', '0', @CATEGORY) 
		WHERE 
		( ( BOOKINGMONTH >= @MONTH_FROM AND BOOKINGYEAR = @YEAR_START AND BOOKINGMONTH-1 < @END_MONTH) OR
		( BOOKINGMONTH-1 < @TO_MONTH AND BOOKINGYEAR = @TO_YEAR AND @TO_YEAR <> @YEAR_START) OR
		( BOOKINGYEAR < @TO_YEAR AND BOOKINGYEAR > @YEAR_START ) )

		IF @TEMP_CURSOR_INIT = 0
		BEGIN
			OPEN @TEMP_CURSOR 
				FETCH NEXT FROM @TEMP_CURSOR
				INTO @SALES_PERSON, @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE
				
				WHILE @@FETCH_STATUS = 0
				BEGIN
					SELECT @ROW_COUNT = COUNT(*) FROM @FN_RESULTS
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR
				
														
						IF @ROW_COUNT = 0 AND  @BL_TYPE = @BACKLOGTYPE
				                
						BEGIN
							INSERT INTO @FN_RESULTS VALUES(@SALES_PERSON, @CUSTOMER, @PRODUCT, @MONTH, 
							@YEAR, @QUANTITY, @QUANTITY*@UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE, NULL)
						END
		
						ELSE
						BEGIN
							SELECT @OLD_QUANTITY = QUANTITY, @OLD_PRICE = UNIT_PRICE FROM @FN_RESULTS
							WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR 
							
	
							UPDATE @FN_RESULTS SET QUANTITY = (@QUANTITY + @OLD_QUANTITY) , UNIT_PRICE = @OLD_PRICE +  ( @QUANTITY * @UNIT_PRICE )
							WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR AND @BL_TYPE = @BACKLOGTYPE
						END
						
					FETCH NEXT FROM @TEMP_CURSOR
					INTO @SALES_PERSON, @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE		
				 END
			
			CLOSE @TEMP_CURSOR
			DEALLOCATE @TEMP_CURSOR
		END 		
	END
	END
  RETURN

END

